/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.BugRS.dao;

import com.aptech.BugRS.enity.ResponseDTO;
import com.aptech.BugRS.util.Configure;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author KISS
 */
public class ResponseDAO {

    private Configure db = null;
    private Connection con = null;
    private ResultSet rs = null;
    private PreparedStatement pst = null;
    private String lastError = "";

    /**
     * Creates a new instance of D_Complaint
     */
    public ResponseDAO() {
        db = new Configure();
    }

    /**
     *
     * @return list of Complaint
     */
    public ArrayList<ResponseDTO> ReadByAll() {
        ArrayList<ResponseDTO> list = new ArrayList<ResponseDTO>();
        con = db.getConnection();
        String sql = "SELECT * FROM Response";
        try {
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                ResponseDTO res = new ResponseDTO();
                res.setComplaintID(rs.getInt(1));
                res.setStaffID(rs.getInt(2));
                res.setResTitle(rs.getString(3));
                res.setResContent(rs.getString(4));
                res.setResDate(rs.getDate(5));
                res.setEstimate(rs.getString(6));
                list.add(res);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error");
        } finally {
            db.closeConnection();
        }
        return list;
    }
//
//    public E_Response ReadByID(int comid, int staffid) {
//        E_Response res = new E_Response();
//        try {
//            con = db.getConnection();
//            String sql = "SELECT * FROM Response WHERE ComplaintID = ? AND StaffID = ?";
//            pst = con.prepareStatement(sql);
//            pst.setInt(1, comid);
//            pst.setInt(2, staffid);
//            rs = pst.executeQuery();
//            res.setComplaintID(rs.getInt(1));
//            res.setStaffID(rs.getInt(2));
//            res.setResTitle(rs.getString(3));
//            res.setResContent(rs.getString(4));
//            res.setResDate(rs.getDate(5));
//            res.setEstimate(rs.getString(6));
//        } catch (SQLException ex) {
//            setLastError("SQL Error");
//        }
//        return res;
//    }

    /**
     * Method add new response
     * @param res
     * @return boolean
     */
    public boolean Create(ResponseDTO res) {
        ArrayList<ResponseDTO> list = ReadByAll();

        /*
         * Check exits in database
         */
        for (ResponseDTO re : list) {
            if (re.getComplaintID() == re.getComplaintID()) {
                setLastError("Response is exitsed! Add fail!");
                return false;
            }
        }
        try {
            con = db.getConnection();
            String sql = "INSERT INTO Response VALUES(?,?,?,?,?,?,?)";
            pst = con.prepareStatement(sql);
            pst.setInt(1, res.getComplaintID());
            pst.setInt(2, res.getStaffID());
            pst.setString(3, res.getResTitle());
            pst.setString(4, res.getResContent());
            pst.setDate(5, res.getResDate());
            pst.setString(6, res.getEstimate());
            if (pst.executeUpdate() > 0) {
                setLastError("Add Response Successfully!");
                db.closeConnection();
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
            db.closeConnection();
            return false;
        }
        setLastError("Add fail!");
        db.closeConnection();
        return false;
    }

    public boolean isExits(ResponseDTO res) {
        try {
            String sql = "SELECT * FROM Response WHERE ComplaintID = ? AND StaffID = ?";
            con = db.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, res.getComplaintID());
            pst.setInt(2, res.getStaffID());
            rs = pst.executeQuery();
            /**
             * Exits return true
             * Not Exit return false;
             */
            if (rs.next()) {
                return true;
            }

        } catch (SQLException ex) {
            setLastError("SQL Error");
        }
        return false;
    }

    /**
     * Method Update a Response
     * @param res
     * @return boolean
     */
    public boolean Update(ResponseDTO res) {
        try {
            con = db.getConnection();
            String sql = "SELECT * FROM Response WHERE ComplaintID = ?";
            pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, res.getComplaintID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateInt(2, res.getStaffID());
            rs.updateString(3, res.getResTitle());
            rs.updateString(4, res.getResContent());
            rs.updateDate(5, res.getResDate());
            rs.updateString(6, res.getEstimate());
            rs.updateRow();
            db.closeConnection();
            setLastError("Update Response successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error");
            db.closeConnection();
            return false;
        }
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
